﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class WaitingListForPlacementBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

//        public void insert(string cr_name, string record_status,int created_by)
//        {
//            try
//            {
//                dbManager.Open();
//                dbManager.CreateParameters(4);
//                dbManager.AddParameters(0, "@cr_name", cr_name);
//                dbManager.AddParameters(1, "@record_status", record_status);
//                dbManager.AddParameters(2, "@created_by", created_by);
//                dbManager.AddParameters(3, "@created_when", DateTime.Today);
           
//                string sql;

//                sql = " insert into hrm_dc_crimes(cr_name,record_status,created_by,created_when) "
//+ " values(@cr_name,@record_status,@created_by,@created_when)";

//                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

//            }

//            catch(Exception ex)
//             {

//                 throw new Exception(ex.Message);  
//            }

//          finally 
//                 {
//                     dbManager.Dispose(); 

//            }

//        }

        
//        public void Update(int cr_seq,string cr_name, string record_status, int updated_by)
//        {
//            try
//            {
//                dbManager.Open();
//                dbManager.CreateParameters(5);
//                dbManager.AddParameters(0, "@cr_name", cr_name);
//                dbManager.AddParameters(1, "@record_status", record_status);
//                dbManager.AddParameters(2, "@cr_seq", cr_seq);
//                dbManager.AddParameters(3, "@updated_by", updated_by);
//                dbManager.AddParameters(4, "@updated_when", DateTime.Today);
                
//                string sql;
                
//                sql = " update hrm_dc_crimes "
//         + "set cr_name=@cr_name,record_status=@record_status,updated_by=@updated_by,updated_when=@updated_when "
//         + " where cr_seq=@cr_seq";

//                dbManager.ExecuteNonQuery(CommandType.Text, sql);

//            }

//            catch (Exception ex)
//            {
//            }

//            finally
//            {
//                dbManager.Dispose();

//            }
//        }
 
        public void Delete(int tmp_emp_seq,int UpdatedBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;               

                sql = " update hrm_tmp_employees set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where tmp_emp_seq = @tmp_emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        ////////// Crimes Dtls /////////////



//        public DataTable SelectDtls()
//        {
//            try
//            {
//                dbManager.Open();
//                string sql;
//                sql = " SELECT "
//+ " hrm_dc_crime_dtls.crd_name,"
//+ " hrm_dc_crimes.cr_name,"
//+ " mas_record_statuses.record_status_name,"
//+ " hrm_dc_crime_dtls.crd_seq,"
//+ " mas_record_statuses.record_status,"
//+ " hrm_dc_crimes.cr_seq"
//+ " FROM"
//+ " hrm_dc_crime_dtls"
//+ " Inner Join hrm_dc_crimes ON hrm_dc_crimes.cr_seq = hrm_dc_crime_dtls.cr_seq"
//+ " Inner Join mas_record_statuses ON hrm_dc_crime_dtls.record_status = mas_record_statuses.record_status"

//+ " where hrm_dc_crime_dtls.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";


////+ " where hrm_dc_crimes.record_status not like '" + SystemInfo._DeleteRecordStatus + "'";


//                DataSet ds = new DataSet();
//                DataTable dt;
//                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
//                dt = ds.Tables[0].Copy();
//                return dt;
//            }

//            catch (Exception ex)
//            {
//                return null;
//            }

//            finally
//            {
//                dbManager.Dispose();
//            }

//        }

        public DataTable Select(Nullable<int> tmp_emp_seq, Nullable<int> emp_status_seq
            , Nullable<int> emp_type_seq, Nullable<int> ent_type_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@ActiveStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", tmp_emp_seq);
                dbManager.AddParameters(2, "@emp_status_seq", emp_status_seq);
                dbManager.AddParameters(3, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(4, "@ent_type_seq", ent_type_seq);

                string sql;
                sql = " SELECT e.* "
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS TMP_EMP_TEXT"
                    + " FROM HRM_TMP_EMPLOYEES e"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE e.RECORD_STATUS = @ActiveStatus"
                    + (tmp_emp_seq.HasValue ? " AND e.TMP_EMP_SEQ = @Sequence" : "")
                    + (emp_status_seq.HasValue ? " AND e.EMP_STATUS_SEQ = @emp_status_seq" : "")
                    + (emp_type_seq.HasValue ? " AND e.EMP_TYPE_SEQ = @emp_type_seq" : "")
                    + (ent_type_seq.HasValue ? " AND e.ENT_TYPE_SEQ = @ent_type_seq" : "");

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

//        public DataTable SelectBySearch(int emp_status_seq,int emp_type_seq,int ent_type_seq)
//        {
//            try
//            {
//                dbManager.Open();
//                string sql;
//                sql = " SELECT e.*, np.PREFIX_NAME"
//                    + " FROM HRM_TMP_EMPLOYEES e "
//                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
//                    + " WHERE e.RECORD_STATUS = '" + SystemInfo._ActiveRecordStatus + "'"
//                        + " AND e.EMP_STATUS_SEQ = " + emp_status_seq
//                        + " AND e.EMP_TYPE_SEQ = " + emp_type_seq 
//                        + " AND e.ENT_TYPE_SEQ = " + ent_type_seq ;

//                DataSet ds = new DataSet();
//                DataTable dt;
//                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
//                dt = ds.Tables[0].Copy();
//                return dt;
//            }

//            catch (Exception ex)
//            {
//                return null;
//            }

//            finally
//            {
//                dbManager.Dispose();
//            }

//        }

        public void InsertTmpPerson(PersonalInfo personInfo,int emp_type_seq, int ent_type_seq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@NamePrefixSeq", personInfo.NamePrefixSeq);
                dbManager.AddParameters(1, "@FirstName", personInfo.FirstName);
                dbManager.AddParameters(2, "@LastName", personInfo.LastName);
                dbManager.AddParameters(3, "@GenderSeq", personInfo.GenderSeq);
                dbManager.AddParameters(4, "@National", personInfo.National);
                dbManager.AddParameters(5, "@BirthDate", personInfo.BirthDate);
                dbManager.AddParameters(6, "@PersonStatusSeq", personInfo.PersonalStatus);
                dbManager.AddParameters(7, "@Citizen", personInfo.Citizen);
                dbManager.AddParameters(8, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@CreatedBy", personInfo.CreatedBy);
                dbManager.AddParameters(10, "@CreatedWhen", DateTime.Today);
                dbManager.AddParameters(11, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(12, "@ent_type_seq", ent_type_seq);
       

                strSQL = "INSERT INTO HRM_TMP_EMPLOYEES(NAME_PREFIX_SEQ, FIRST_NAME, LAST_NAME"
                          + ", GENDER_SEQ, NAT_SEQ, BIRTH_DATE, EMP_STATUS_SEQ, CITIZEN, ENT_TYPE_SEQ"
                          + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN, EMP_TYPE_SEQ)"
                   
                          + " VALUES(@NamePrefixSeq, @FirstName, @LastName, @GenderSeq"
                          + ", @National, @BirthDate, @PersonStatusSeq, @Citizen, @ent_type_seq"
                          + ", @RecordStatus, @CreatedBy, @CreatedWhen,@emp_type_seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        
        public void UpdateTmpPerson(PersonalInfo personInfo, int tmp_emp_seq,int emp_type_seq, int ent_type_seq, int updated_by)
        {
            string strSQL;


            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

               dbManager.AddParameters(0, "@NamePrefixSeq", personInfo.NamePrefixSeq);
                dbManager.AddParameters(1, "@FirstName", personInfo.FirstName);
                dbManager.AddParameters(2, "@LastName", personInfo.LastName);
                dbManager.AddParameters(3, "@GenderSeq", personInfo.GenderSeq);
                dbManager.AddParameters(4, "@National", personInfo.National);
                dbManager.AddParameters(5, "@BirthDate", personInfo.BirthDate);
                dbManager.AddParameters(6, "@PersonStatusSeq", personInfo.PersonalStatus);
                dbManager.AddParameters(7, "@Citizen", personInfo.Citizen);
                dbManager.AddParameters(8, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(9, "@updated_by", updated_by);
                dbManager.AddParameters(10, "@updated_when", DateTime.Today);
                dbManager.AddParameters(11, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(12, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(13, "@ent_type_seq", ent_type_seq);
                
                strSQL = "update hrm_tmp_employees "
                   + " set name_prefix_seq=@NamePrefixSeq,"
                    + " first_name=@FirstName, " 
                    + " last_name=@LastName,"
                    + " gender_seq=@GenderSeq,"
                    + " nat_seq=@National,"
                    + " birth_date=@BirthDate,"
                    + " emp_status_seq=@PersonStatusSeq,"
                    + " citizen=@Citizen,"
                    + " ent_type_seq = @ent_type_seq, "
                    + " emp_type_seq = @emp_type_seq, "
                    + " record_status=@RecordStatus,"
                    + " updated_by=@updated_by,"
                    + " updated_when=@updated_when"                    
                    + " where tmp_emp_seq = @tmp_emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message); 
            }
            finally
            {
                dbManager.Dispose();
            }
        }       

        public DataTable SelectStatusCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from HRM_PS_EMPLOYEE_STATUSES  "
                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable SelectEntryTypeCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from HRM_RC_ENTRY_TYPES  "
                    + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }


        public DataTable SelectTmpEducation(int tmp_emp_seq)
        {
            try
            {
                string sql;
                dbManager.Open();
                
                sql = " SELECT edu.tmp_edu_seq,"
                        + " qual.edu_degree_seq, edu.edu_qual_seq, edu.edu_ins_seq, edu.edu_major_seq,"
                        + " de.edu_degree_name AS degree,"
                        + " major.edu_major_name AS major,"
                        + " ins.edu_ins_name as ins_name,"
                        + " qual.edu_qual_name AS qual"
                    + " FROM hrm_tmp_employee_educations AS edu"                       
                        + " Left Join mas_edu_majors AS major ON major.edu_major_seq = edu.edu_major_seq"
                        + " Left Join mas_edu_institutes AS ins ON ins.edu_ins_seq = edu.edu_ins_seq"
                        + " Left Join mas_edu_qualifications AS qual ON qual.edu_qual_seq = edu.edu_qual_seq"
                        + " Left Join mas_edu_degrees AS de ON qual.edu_degree_seq = de.edu_degree_seq"
                    + " where edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                        + " and edu.tmp_emp_seq = " + tmp_emp_seq ;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
                
        public void DeleteEdu(int tmp_edu_seq, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;


                sql = " update hrm_tmp_employee_educations set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where tmp_edu_seq = @tmp_edu_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertEdu(int tmp_emp_seq,int edu_qual_seq,int edu_major_seq,int edu_ins_seq,int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._ActiveRecordStatus  );
                dbManager.AddParameters(2, "@created_by", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Today);
                dbManager.AddParameters(4, "@edu_qual_seq", edu_qual_seq);
                dbManager.AddParameters(5, "@edu_major_seq", edu_major_seq);
                dbManager.AddParameters(6, "@edu_ins_seq", edu_ins_seq);

                string sql;

                sql = " INSERT INTO HRM_TMP_EMPLOYEE_EDUCATIONS"
                + "(TMP_EMP_SEQ,RECORD_STATUS,"
                + " CREATED_BY,CREATED_WHEN,EDU_QUAL_SEQ,EDU_MAJOR_SEQ,EDU_INS_SEQ) "
                + " values(@tmp_emp_seq,@record_status,@created_by,@created_when, "
                + " @edu_qual_seq,@edu_major_seq,@edu_ins_seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }
        public void UpdateEdu(int tmp_edu_seq, int edu_qual_seq, int edu_major_seq, int edu_ins_seq, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(1, "@updated_by", updated_by);
                dbManager.AddParameters(2, "@updated_when", DateTime.Today);
                dbManager.AddParameters(3, "@edu_qual_seq", edu_qual_seq);
                dbManager.AddParameters(4, "@edu_major_seq", edu_major_seq);
                dbManager.AddParameters(5, "@edu_ins_seq", edu_ins_seq);

                string sql;

                sql = " UPDATE HRM_TMP_EMPLOYEE_EDUCATIONS"
                    + " SET EDU_QUAL_SEQ = @edu_qual_seq"
                        + ", EDU_MAJOR_SEQ = @edu_major_seq"
                        + ", EDU_INS_SEQ = @edu_ins_seq"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE TMP_EDU_SEQ = @tmp_edu_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }
        }

        

        //public DataTable GetQual(int edu_qual_seq)
        //{

        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = " select * from mas_edu_qualifications  "
        //            + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
        //            + " and edu_qual_seq= " + edu_qual_seq;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }


        //}

        //public DataTable GetMajor(int edu_major_seq)
        //{

        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = " select * from mas_edu_majors  "
        //            + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
        //            + " and edu_major_seq= " + edu_major_seq;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }


        //}
        //public DataTable GetIns(int edu_ins_seq)
        //{

        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = " select * from mas_edu_institutes  "
        //            + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
        //            + " and edu_ins_seq= " + edu_ins_seq;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //}
        //public DataTable GetIns(int edu_ins_seq)
        //{

        //    try
        //    {
        //        dbManager.Open();
        //        string sql;
        //        sql = " select * from mas_edu_institutes  "
        //            + " where record_status = '" + SystemInfo._ActiveRecordStatus + "'"
        //            + " and edu_ins_seq= " + edu_ins_seq;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }


        //}
   
    }
}

